package cn.edu.ctbu.lab.dao;

import cn.edu.ctbu.lab.domain.Score;
import cn.edu.ctbu.lab.domain.Student;
import cn.edu.ctbu.lab.vo.CourseExt;
import cn.edu.ctbu.lab.vo.ScoreExt;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ScoreRepository extends JpaRepository<Score,Integer> {

    @Query(value="SELECT sc.id,sc.score,sc.student_id AS studentId,st.`name` AS studentName,st.`no` AS studentNo FROM tb_score AS sc \n" +
            "LEFT JOIN tb_student AS st ON sc.student_id = st.id  where sc.course_id= :courseId  limit :start ,:pageSize ",nativeQuery = true)
    public List<ScoreExt> getByPage(Integer courseId, Integer start, Integer pageSize);

    @Query(value = "SELECT count(*)" +
            "FROM tb_score AS sc LEFT JOIN tb_student AS st ON sc.student_id = st.id ",nativeQuery = true)
    public Integer getByPageCount();
}
